Cannot approve documents after upgrade

• Content Studio ver. 5.2 RC 1

After an upgrade from Content Studio 5.0 or 5.1 the following message may appear when you try to approve a document or synchronize files the operation fails with a timeout error.

SqlException: The timeout period elapsed prior to completion of the operation or the server is not responding.
 

When a Content Studio database is upgrade to version 5.2 the datatype of the ContentBinary column is changed from image to varbinary(max). The reason for this is to support automatic background change tracking for the full text search functionality. The fulltext search is performed in Content Studio against the ContentBinary column in the tbl_modules_content table. It stores the binary representation of the document when it is stored on disc which makes it possible to search for words in the content regardless of the document type being stored. This content can be very large and for this reason it is written in chunks to the database using the internal stored procedure [dbo].[pp_UpdateBinaryContent]. When the database column is upgraded this procedure is changed to support the new method used with the varbinary(max) data type. When the first call is made after the upgrade SQL Server can have severe difficulties to create a valid execution plan for this stored procedure and this process can take a very long time causing Content Studio to report a time-out problem.

We have found that when Sql Server has completed a successful query-plan for the first time the problem disappears. You can do this from SQL Server Management Studio against the upgraded database with the SQL statements below. The value <ID> must be replaced with a valid document id and this document must exists. Since this statement updates the binary representation of the document you must not use any document that has any value to your web site.

DECLARE @documentId int, @cid uniqueidentifier, @ContentBinary varbinary(max), @WithDelete bit;
--********** Change this value to valid 'crap' document
SELECT @documentId = <ID>;
--**********
SELECT @cid = ISNULL([dbo].[f_ContentApproved](@documentId), dbo.f_ContentToEdit(@documentId));
SELECT @ContentBinary = 0x112233445566778899aabbccddeeff,
       @WithDelete = 1;
DECLARE @Offset bigint;
SELECT @Offset = CASE @WithDelete WHEN 1 THEN 0 ELSE NULL END;
UPDATE [dbo].[tbl_modules_content] 
       SET ContentBinary .WRITE (@ContentBinary, @Offset, NULL)
       WHERE ContentID = @CID
       OPTION (RECOMPILE)
                

Teknikhuset suspects that this is a problem related to the way SQL Server caches compile queries i.e. query plans. If there is an old query plan from the time before the upgrade the query will fail since it no longer is valid.

The installation script for Content Studio 5.2 Release (build 5007) executes the script above as a part of the upgrade process.